﻿CREATE PROCEDURE [dbo].[GetRentPropertyQTY]
@RentType NVARCHAR (3), @PriceMax BIGINT=0, @PriceMin BIGINT=0, @Project NVARCHAR (30)='', @FloorSize INT=0, @LandSize INT=0, @Location NVARCHAR (30)='', @FloorSizeMin INT=0, @LandSizeMin INT=0
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from

 -- interfering with SELECT statements.

 SET NOCOUNT ON;


    -- Insert statements for procedure here

IF @RentType='RR'

select count(a.propertyid) as RecordCount
from aw_property a
left join aw_road b on a.roadid = b.roadid



left join aw_location c on b.locationid = c.locationid



left join aw_state d on c.stateid = d.stateid



left join aw_propertytype e on a.propertytypeid = e.typeid



left join aw_staff f on a.rlisterid = f.staffid



left join AW_Project h on a.ProjectId = h.ProjectId



where a.rentview = 1 



--and b.statusdelete = 0 and c.statusdelete=0 



--and d.statusdelete = 0 and e.statusdelete = 0 



and f.statusdelete = 0



and (a.RRYes = 1)



and a.RRelistStatus = 0 and a.AgentFeedbackRent is null



and (a.RRAmt <= @PriceMax or @PriceMax = 0)



and (a.RRAmt >= @PriceMin  or @PriceMin = 0)

and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)



and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')



and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')



ELSE IF @RentType = 'RL'



select count(a.propertyid) as RecordCount



from aw_property a



left join aw_road b on a.roadid = b.roadid



left join aw_location c on b.locationid = c.locationid



left join aw_state d on c.stateid = d.stateid



left join aw_propertytype e on a.propertytypeid = e.typeid



left join aw_staff f on a.rlisterid = f.staffid



left join AW_Project h on a.ProjectId = h.ProjectId



where a.rentview = 1 



--and b.statusdelete = 0 and c.statusdelete=0 



--and d.statusdelete = 0 and e.statusdelete = 0 



and f.statusdelete = 0



and (a.RIYes=1)



and a.RRelistStatus = 0 and a.AgentFeedbackRent is null



and (a.RLAmt <= @PriceMax or @PriceMax = 0)



and (a.RLAmt >= @PriceMin  or @PriceMin = 0)



and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)



and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')



and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')



ELSE IF @RentType = 'RI'



select count(a.propertyid) as RecordCount



from aw_property a



left join aw_road b on a.roadid = b.roadid



left join aw_location c on b.locationid = c.locationid



left join aw_state d on c.stateid = d.stateid



left join aw_propertytype e on a.propertytypeid = e.typeid



left join aw_staff f on a.rlisterid = f.staffid



left join AW_Project h on a.ProjectId = h.ProjectId



where a.rentview = 1 



--and b.statusdelete = 0 and c.statusdelete=0 



--and d.statusdelete = 0 and e.statusdelete = 0 



and f.statusdelete = 0



and (a.RIYes=1)



and a.RRelistStatus = 0 and a.AgentFeedbackRent is null



and (a.RIAmt <= @PriceMax or @PriceMax = 0)



and (a.RIAmt >= @PriceMin  or @PriceMin = 0)


and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)



and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')



and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')







ELSE IF @RentType = 'RC'



select count(a.propertyid) as RecordCount



from aw_property a



left join aw_road b on a.roadid = b.roadid



left join aw_location c on b.locationid = c.locationid



left join aw_state d on c.stateid = d.stateid



left join aw_propertytype e on a.propertytypeid = e.typeid



left join aw_staff f on a.rlisterid = f.staffid



left join AW_Project h on a.ProjectId = h.ProjectId



where a.rentview = 1 



--and b.statusdelete = 0 and c.statusdelete=0 



--and d.statusdelete = 0 and e.statusdelete = 0 



and f.statusdelete = 0



and (a.RCYes=1)



and a.RRelistStatus = 0 and a.AgentFeedbackRent is null



and (a.RCAmt <= @PriceMax or @PriceMax = 0)



and (a.RCAmt >= @PriceMin  or @PriceMin = 0)



and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)



and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')



and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')







ELSE



select 'WRONG INPUT PARAMETER(RR/RL/RC/RI)' AS NORESULT 



END
